<?php
require_once $_SESSION["app_root"]."config/app.config.php";
class EntityDAO
{
    /**
     * Builds the coresponding EntityInfo DAO for the $ID
     * @return Returned object is an EntityInfo DAO
     * @param string $ID is the string representation <b>uuid</b> of the entity
     * @param object $getFields[optional] if is true it also gets the fields
     */
    public function GetBaseByID($ID, $getFields = false)
    {
        $entity = null;
        if ( isset ($_SESSION["SessionUser"]))
        {
            $db = GetDatabase();
            $query = "
						SELECT
							cms_entities.*
						FROM 
							cms_entities 
						WHERE
							(
								(`cms_entities`.`OwnerID` = '".$_SESSION["SessionUser"]->getID()."' AND `cms_entities`.`u` > 0) OR
								(`cms_entities`.`GroupID` IN (	SELECT 
																	`cms_user_groups`.`GroupID` 
																FROM 
																	`cms_user_groups` 
																WHERE 
																	`cms_user_groups`.`UserID` = '".$_SESSION["SessionUser"]->getID()."') AND 
									`cms_entities`.`g` > 0) OR
								(`cms_entities`.`w` > 0)
							) AND
							id = '".$ID."'";
            $rs = $db->SelectLimit($query, 1, 0);
            $result = $rs->FetchRow();
            if ($result)
            {
                $entity = $this->EntityDataReader($result);
            } else
            {
                throw new Exception("Check permissions for this entity: ".$ID);
            }
            if ($getFields)
            {
                $entityFields = new EntityFieldDAO();
                $entity->setFields($entityFields->GetAllByEntityID($ID, "GridLocation"));
            }
            $db->close();
        } else
        {
            throw new Exception("Unauthorized access.");
        }
        return $entity;
    }

    public function GetAll($getFields = false)
    {
        if ( isset ($_SESSION["SessionUser"]))
        {
            $db = GetDatabase();
            $query = "
						SELECT
							cms_entities.*
						FROM 
							cms_entities 
						WHERE
							(
								(`cms_entities`.`OwnerID` = '".$_SESSION["SessionUser"]->getID()."' AND `cms_entities`.`u` > 0) OR
								(`cms_entities`.`GroupID` IN (	SELECT 
																	`cms_user_groups`.`GroupID` 
																FROM 
																	`cms_user_groups` 
																WHERE 
																	`cms_user_groups`.`UserID` = '".$_SESSION["SessionUser"]->getID()."') AND 
									`cms_entities`.`g` > 0) OR
								(`cms_entities`.`w` > 0)
							)";
            $rs = $db->Execute($query);
            $entities = array ();
            if ($rs)
            {
                foreach ($rs as $result)
                {
                    $entity = $this->EntityDataReader($result);
                    if ($getFields)
                    {
                        $entityFields = new EntityFieldDAO();
                        $entity->setFields($entityFields->GetAllByEntityID($entity->getID(), "FormLocation"));
                    }
                    array_push($entities, $entity);
                }
            } else
            {
                throw new Exception("Check permissions for this entity: ".$ID);
            }
            $db->close();
        } else
        {
            throw new Exception("Unauthorized access.");
        }
        return $entities;
    }

    public function EntityDataReader($result)
    {
        $entity = new EntityDVO($result['ID'],
        $result['Name'],
        $result['Label'],
        $result['PluralLabel'],
        $result['TableName'],
        $result['CreatedBy'],
        $result['CreatedDate'],
        $result['ModifiedBy'],
        $result['ModifiedDate'],
        $result['OwnerID'],
        $result['GroupID'],
        $result['Status'],
        $result['u'], $result['g'], $result['w']);
        return $entity;
    }

    /**
     *
     * @return
     * @param EntityDVO $entity
     */
    public function Create($entity)
    {
        //vp($entity);
        $db = GetDatabase();
        $tableExists = $db->Execute("SHOW TABLES LIKE '".$entity->getTableName()."'");
        if ($tableExists->RecordCount() == 0)
        {
            $sqlInsertEntity =
            "
			INSERT INTO cms_entities 
				(`ID`, 
				`Name`, 
				`Label`, 
				`PluralLabel`,
				`TableName`,
				`CreatedBy`,
				`CreatedDate`,
				`ModifiedBy`,
				`ModifiedDate`,
				`OwnerID`,
				`GroupID`,
				`Status`,
				`U`, `G`, `W`)
			VALUES 
				(
				'".uuid()."',
				'".$entity->getName()."',
				'".$entity->getLabel()."',
				'".$entity->getPluralLabel()."',
				'".$entity->getTableName()."',
				'".$entity->getCreatedBy()."',
				'".$entity->getCreatedDate()."',
				'".$entity->getModifiedBy()."',
				'".$entity->getModifiedDate()."',
				'".$entity->getOwnerID()."',
				'".$entity->getGroupID()."',
				'".$entity->getStatus()."',
				'".$entity->getU()."',
				'".$entity->getG()."',
				'".$entity->getW()."'
				)";
            $sqlCreateEntityTable = "
			CREATE TABLE `".$entity->getTableName()."` 
				(`ID` VARCHAR(36) NOT NULL
				,`CreatedBy` VARCHAR(36) NOT NULL
				,`CreatedDate` VARCHAR(15) NOT NULL
				,`ModifiedBy` VARCHAR(36) NOT NULL
				,`ModifiedDate` VARCHAR(15) NOT NULL
				,`OwnerID` VARCHAR(36) NOT NULL
				,`GroupID` VARCHAR(36) NOT NULL
				,`Status`tinyint(4) NOT NULL DEFAULT '1'
				,`u` INT(1) NOT NULL DEFAULT '0'
				,`g` INT(1) NOT NULL DEFAULT '0'
				,`w` INT(1) NOT NULL DEFAULT '0'
				,PRIMARY KEY (`ID`)
				,KEY `CreatedBy` (`CreatedBy`)
				,KEY `ModifiedBy` (`ModifiedBy`)
				,KEY `Owner` (`OwnerID`)
				,KEY `GroupID` (`GroupID`)
		    ) ENGINE = InnoDB DEFAULT CHARSET = latin1 ROW_FORMAT = DYNAMIC";
            $CreatedByKey = "ALTER TABLE `".$entity->getTableName()."` ADD FOREIGN KEY ( `CreatedBy` ) REFERENCES `cms_user_accounts` (`ID`)";
            $ModifiedByKey = "ALTER TABLE `".$entity->getTableName()."` ADD FOREIGN KEY ( `ModifiedBy` ) REFERENCES `cms_user_accounts` (`ID`)";
            $OwnerIDKey = "ALTER TABLE `".$entity->getTableName()."` ADD FOREIGN KEY ( `OwnerID` ) REFERENCES `cms_user_accounts` (`ID`)";
            $GroupIDKey = "ALTER TABLE `".$entity->getTableName()."` ADD FOREIGN KEY ( `GroupID` ) REFERENCES `cms_groups` (`ID`)";
            $db->StartTrans();
            $db->Execute($sqlInsertEntity);
            $db->Execute($sqlCreateEntityTable);
            $db->Execute($CreatedByKey);
            $db->Execute($ModifiedByKey);
            $db->Execute($OwnerIDKey);
            $db->Execute($GroupIDKey);
            $db->CompleteTrans();
            return true;
        } else
        {
            return false;
        }
    }

    public function GetRecords($entityID, $start, $limit, $queryString)
    {
        $entity = $this->GetBaseByID($entityID, true);
        //		$query = "
        //				SELECT ID, SpecName FROM `".$entity->getTableName()."` LIMIT ".$start.", ".$limit."
        //				";
        $fieldsToSelect = "`ID`";
        $whereStr = "0 ";
        foreach ($entity->getFields() as $field)
        {
            if ($field->getGridLocation() > 0)
            {
                $fieldsToSelect .= ", `".$field->getDbField()."`";
            }
            $whereStr .= "OR `".$field->getDbField()."` LIKE '%$queryString%' ";
        }
        $fieldsToSelect .= ", `CreatedBy`, `CreatedDate`, `ModifiedBy`, `ModifiedDate`, `OwnerID`, `Status`, `u`, `g`, `w`";
        $query = "
				SELECT 
					".$fieldsToSelect." 
				FROM 
					`".$entity->getTableName()."`
				WHERE
					".$whereStr."
				LIMIT ".$start.", ".$limit."
				";
        $count = "SELECT 
						COUNT(ID) 
					FROM 
						`".$entity->getTableName()."`
					WHERE
					".$whereStr;
        $db = GetDatabase();
        $counter = $db->Execute($count);
        $rs = $db->Execute($query);
        $result = array ();
        $counterArr = $counter->GetArray();
        $result["TotalRecords"] = $counterArr[0]["COUNT(ID)"];
        $result["Records"] = $rs->GetArray();
        return $result;
    }
}





?>
